package com.fanghaoran.dao;

import com.fanghaoran.bean.Interaction;
import com.fanghaoran.entity.Book;
import com.fanghaoran.entity.Users;
import com.fanghaoran.entity.Words;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: win11
 * @Date: 2023/03/27/22:39
 * @Description: 管理员操作业务的数据库操作
 */
public class ManagerDao {
    //管理员获取用户信息

    /**
     * 管理员获取全部用户信息
     */
    public Interaction getUserInfo() throws Exception {
        String sql = "select * from user_info where manager=0;";
        List<Users> usersList = BaseDao.executeQuery(Users.class, sql);
        return new Interaction(null, usersList);
    }

    /**
     * 管理员通过用户名获取特定用户信息
     */
    public Interaction getUserInfoByUsername(String username) throws Exception {
        String sql = "select * from user_info where username like ? and manager=0;";
        List<Users> usersList = BaseDao.executeQuery(Users.class, sql, "%" + username + "%");
        return new Interaction(null, usersList);
    }

    /**
     * 管理员通过电话号码获取特定用户信息
     */
    public Interaction getUserInfoByTelephone(String telephone) throws Exception {
        String sql = "select * from user_info where telephone like ? and manager=0;";
        List<Users> usersList = BaseDao.executeQuery(Users.class, sql, "%" + telephone + "%");
        return new Interaction(null, usersList);
    }

    /**
     * 管理员通过昵称获取特定用户信息
     */
    public Interaction getUserInfoByNickname(String nickname) throws Exception {
        String sql = "select * from user_info where nickname like ? and manager=0;";
        List<Users> usersList = BaseDao.executeQuery(Users.class, sql, "%" + nickname + "%");
        return new Interaction(null, usersList);
    }

    /**
     * 管理员拉黑用户
     */
    public void updateBlocked(Long newValue, String username) throws SQLException {
        String sql = "update user_info set blocked=? where username=?;";
        BaseDao.executeUpdate(sql, newValue, username);
    }

    //单词的增删查改

    /**
     * 查询单词数据表中是否有某个单词
     */
    public Interaction selectWord(String word) throws Exception {
        String sql = "select * from word where word=?;";
        List<Words> words = BaseDao.executeQuery(Words.class, sql, word);
        return new Interaction(null, words);
    }

    /**
     * 查询某本单词书中是否有某个单词
     */
    public Interaction selectWord(String word, String bookName) throws Exception {
        String sql = "select word.word from word_book join word on word_book.word_id=word.id " +
                "join book on word_book.book_id=book.id where book.name = ? and word.word = ?;";
        List<Words> words = BaseDao.executeQuery(Words.class, sql, bookName, word);
        return new Interaction(null, words);
    }

    /**
     * 搜索某本单词书的所有单词
     */
    public Interaction selectWordOfBook(String bookName) throws Exception {
        String sql = "select word.id,word.word,word.translation " +
                "from word_book " +
                "join word on word_book.word_id=word.id " +
                "join book on word_book.book_id=book.id " +
                "where book.name=?;";
        List<Words> words = BaseDao.executeQuery(Words.class, sql, bookName);
        return new Interaction(null, words);
    }

    /**
     * 添加单词到单词库中
     *
     * @param word        单词
     * @param translation 释义
     */
    public void insertWordToWord(String word, String translation) throws SQLException {
        String sql = "insert into word(word,translation) values(?,?);";
        BaseDao.executeUpdate(sql, word, translation);
    }

    /**
     * 添加单词到单词书中(只更新单词_单词书中间表)
     *
     * @param word     单词
     * @param bookName 释义
     */
    public void insertWordToBook(String word, String bookName) throws SQLException {
        String sql = "insert into word_book(word_id,book_id) " +
                "values((select id from word where word=?),(select id from book where name=?));";
        BaseDao.executeUpdate(sql, word, bookName);
    }

    /**
     * 删除某本单词书中的单词(只是删除该单词和该单词书的中间表记录)
     *
     * @param word     单词
     * @param bookName 释义
     */
    public void deleteWordFromBook(String word, String bookName) throws SQLException {
        //并不是真的删除单词库中的单词数据,只是删除该单词和该单词书的中间表记录
        String sql = "delete from word_book " +
                "where word_id= " +
                "(select id from word where word=?) " +
                "and book_id= " +
                "(select id from book where name=?);";
        BaseDao.executeUpdate(sql, word, bookName);
    }

    /**
     * 修改单词库中的单词
     *
     * @param word        修改后的单词
     * @param translation 修改后的释义
     * @param preWord     修改前的单词
     */
    public void modifyWord(String word, String translation, String preWord) throws SQLException {
        String sql = "update word set word=?,translation=? where word=?;";
        BaseDao.executeUpdate(sql, word, translation, preWord);
    }


    //单词书的添加和查看

    /**
     * 获取单词书列表
     */
    public Interaction getBookInfo() throws Exception {
        String sql = "select * from book;";
        List<Book> booksList = BaseDao.executeQuery(Book.class, sql);
        return new Interaction(null, booksList);
    }

    /**
     * 查询具体某本书的信息
     *
     * @param bookName 书名
     */
    public Interaction selectBook(String bookName) throws Exception {
        String sql = "select * from book where name=?;";
        List<Book> books = BaseDao.executeQuery(Book.class, sql, bookName);
        return new Interaction(null, books);
    }

    /**
     * 添加书籍
     *
     * @param bookName 书籍的名字
     */
    public void insertBook(String bookName) throws SQLException {
        String sql = "insert into book(name) values(?);";
        BaseDao.executeUpdate(sql, bookName);
    }
}
